﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using STPlatform.UPracticalMSSQLDatabaseV2;

namespace SimpleORM
{
    public class DataSourceSchema : CDObject, IDataSourceSchema
    {
        #region Methods

        #region Methods - Public

        public virtual void Export(Type elementType)
        {
            throw new NotImplementedException();
        }

        public virtual Type Import(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual DynamicClassBuilder ImportBuilder(string schema, string tableName)
        {
            throw new NotImplementedException();
        }

        public virtual void CreateElement(Type elementType)
        {
            throw new NotImplementedException();
        }

        public virtual void DeleteElement(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual void RenameElement(string schema, string elementName, string elementNameNew)
        {
            throw new NotImplementedException();
        }

        public virtual bool AnyElement(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual void CreateElementProperty(string schema, string elementName, DataColumnAttribute property)
        {
            throw new NotImplementedException();
        }

        public virtual void DeleteElementProperty(string schema, string elementName, string propertyName)
        {
            throw new NotImplementedException();
        }

        public virtual void ChangeElementProperty(string schema, string elementName, DataColumnAttribute property)
        {
            throw new NotImplementedException();
        }

        public virtual bool AnyElementProperty(string schema, string elementName, string propertyName)
        {
            throw new NotImplementedException();
        }

        public virtual List<string> GetElementPrimaryPropertyNames(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual List<string> GetElementPropertyNames(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual List<DataColumnAttribute> GetElementProperties(string schema, string elementName)
        {
            throw new NotImplementedException();
        }

        public virtual List<DataTableAttribute> GetElements()
        {
            throw new NotImplementedException();
        }

        public virtual List<ElementRelationship> GetElementRelationships()
        {
            var list = GetElementRelationshipsDirection().ToList();
            var listR = new List<ElementRelationship>();
            list.ForEach(c => listR.Add(new ElementRelationship
            {
                PrimarySchema = c.ForeignSchema,
                PrimaryElementName = c.ForeignElementName,
                PrimaryColumnName = c.ForeignColumnName,
                ForeignSchema = c.PrimarySchema,
                ForeignElementName = c.PrimaryElementName,
                ForeignColumnName = c.PrimaryColumnName,
            }));

            list.AddRange(listR);
            return list;
        }

        public virtual List<ElementRelationship> GetElementRelationshipsDirection()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Methods - Protected

        protected virtual string FormatSchema(string schema)
        {
            return schema;
        }

        protected virtual string FormatElementName(string elementName)
        {
            return elementName;
        }

        protected virtual string FormatElementNameToTypeName(string elementName)
        {
            return elementName;
        }

        protected virtual string FormatFieldNameToPropertyName(string fieldName)
        {
            return fieldName;
        }

        #endregion

        #endregion
    }
    public abstract class DbSchema : DataSourceSchema, IDbSchema
    {
        #region Properties

        public IDbContext DataSource { get; set; }

        #endregion

        #region Fields

        private static readonly Hashtable htTypes = new Hashtable();

        #endregion

        #region Ctor

        public DbSchema(IProviderDatabase provider)
        {
            //DataSource = YuLinTu.Data.DataSource.Create<
            //    IDbContext>(provider.Name, provider.ConnectionString);
        }

        #endregion

        #region Methods

        #region Methods - Public

        public int Save()
        {
            return (int)DataSource.Queries.Save();
        }

        public override void Export(Type elementType)
        {
            var ht = GetDataSourceTypes();
            lock (ht)
            {
                var oc = ObjectContext.Create(elementType);
                if (!AnyElement(oc.Schema, oc.TableName))
                {
                    CreateElement(elementType);
                    return;
                }

                var colNames = GetElementPropertyNames(oc.Schema, oc.TableName);
                foreach (var colName in colNames)
                {
                    if (!oc.Columns.Any(c => c.Value.DataColumn.ColumnName == colName))
                        DeleteElementProperty(oc.Schema, oc.TableName, colName);
                    else
                        ChangeElementProperty(oc.Schema, oc.TableName, oc.Columns.First(
                            c => c.Value.DataColumn.ColumnName == colName).Value.DataColumn);
                }

                foreach (var col in oc.Columns)
                {
                    if (colNames.Contains(col.Value.DataColumn.ColumnName))
                        continue;

                    CreateElementProperty(oc.Schema, oc.TableName, col.Value.DataColumn);
                }

                Save();

                var table = new DataTableAttribute(oc.Schema, oc.TableName);
                if (ht.ContainsKey(table.FullName))
                    ht.Remove(table.FullName);
            }
        }

        public override Type Import(string schema, string tableName)
        {
            //schema = FormatSchema(schema);
            //tableName = FormatElementName(tableName);
            //string typeName = FormatElementNameToTypeName(tableName);

            //var ht = GetDataSourceTypes();
            //lock (ht)
            //{
            //    var table = new DataTableAttribute(schema, tableName);
            //    table.AliasName = typeName;

            //    if (ht.ContainsKey(table.FullName))
            //        return (Type)ht[table.FullName];

            //    var cols = GetElementProperties(schema, tableName);
            //    var type = DynamicDataTypeBuilder.BuildType(
            //        string.Format("YuLinTu.Data.DynamicModels_{0}",
            //        DataSource.DataSource.ConnectionString.ToMD5String()), table, cols);

            //    ht[table.FullName] = type;
            //    return type;
            return null;
            //}
        }

        //public override DynamicClassBuilder ImportBuilder(string schema, string tableName)
        //{
        //    schema = FormatSchema(schema);
        //    tableName = FormatElementName(tableName);
        //    string typeName = FormatElementNameToTypeName(tableName);

        //    var table = new DataTableAttribute(schema, typeName);
        //    var cols = GetElementProperties(schema, tableName);

        //    return DynamicDataTypeBuilder.BuildBuilder(
        //        string.Format("YuLinTu.Data.DynamicModels_{0}",
        //        DataSource.DataSource.ConnectionString.ToMD5String()), table, cols);
        //}

        #endregion

        #region Methods - Private

        private Hashtable GetDataSourceTypes()
        {
            lock (htTypes)
            {
                string key = DataSource.DataSource.ConnectionString;

                if (!htTypes.ContainsKey(key))
                    htTypes[key] = new Hashtable();

                return (Hashtable)htTypes[key];
            }
        }

        #endregion

        #endregion
    }
    public class SqlServerSchema : DbSchema
    {
        #region Ctor

        public SqlServerSchema(IProviderDatabase db)
            : base(db)
        {
        }

        #endregion

        #region Fields

        private const string stringExtendAliasName = "AliasName";

        #endregion

        #region Methods

        #region Methods - Public

        public override bool AnyElement(string schema, string tableName)
        {
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();

            var qc = from sch in qcSS
                     join obj in qcSO on sch.SchemaID equals obj.SchemaID
                     where sch.Name == schema && obj.Name == tableName && (obj.Type == "U" || obj.Type == "V")
                     select obj.Name;

            return qc.Any();
        }

        public override void DeleteElement(string schema, string tableName)
        {
            var qc = DataSource.CreateQuery();
            var script = DataSource.DataSource.
                CreateScriptFactory().DropObject(schema, tableName);

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override void CreateElement(Type elementType)
        {
            var oc = ObjectContext.Create(elementType);
            var qc = DataSource.CreateQuery();

            var script = DataSource.DataSource.CreateScriptFactory().CreateObject(
                oc.Schema, oc.TableName, oc.Columns.Select(c => c.Value.DataColumn).ToArray());

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override void RenameElement(string schema, string objName, string objNameNew)
        {
            var qc = DataSource.CreateQuery();

            var script = DataSource.DataSource.
                CreateScriptFactory().RenameObject(schema, objName, objNameNew);

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override void CreateElementProperty(string schema, string objName, DataColumnAttribute col)
        {
            var qc = DataSource.CreateQuery();

            var script = DataSource.DataSource.
                CreateScriptFactory().CreateProperty(schema, objName, col);

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override void DeleteElementProperty(string schema, string objName, string colName)
        {
            var qc = DataSource.CreateQuery();

            var script = DataSource.DataSource.
                CreateScriptFactory().DropProperty(schema, objName, colName);

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override void ChangeElementProperty(string schema, string objName, DataColumnAttribute col)
        {
            var qc = DataSource.CreateQuery();

            var script = DataSource.DataSource.
                CreateScriptFactory().AlterProperty(schema, objName, col);

            qc.CommandContext.CommandText.Append(script.CommandText);
            qc.CommandContext.ExecuteArgument = script.ExecuteArgument;
            qc.CommandContext.Type = script.Type;

            DataSource.Queries.Add(qc);
        }

        public override bool AnyElementProperty(string schema, string tableName, string colName)
        {
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSC = DataSource.CreateQuery<SqlServerColumnsTableElement>();

            var qc = from sch in qcSS
                     join obj in qcSO on sch.SchemaID equals obj.SchemaID
                     join col in qcSC on obj.ObjectID equals col.ObjectID
                     where sch.Name == schema && obj.Name == tableName && (obj.Type == "U" || obj.Type == "V") && col.Name == colName
                     select col.Name;

            return qc.Any();
        }

        public override List<string> GetElementPropertyNames(string schema, string tableName)
        {
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSC = DataSource.CreateQuery<SqlServerColumnsTableElement>();

            var qc = from sch in qcSS
                     join obj in qcSO on sch.SchemaID equals obj.SchemaID
                     join col in qcSC on obj.ObjectID equals col.ObjectID
                     where sch.Name == schema && obj.Name == tableName && (obj.Type == "U" || obj.Type == "V")
                     select col.Name;

            return qc.ToList();
        }

        public override List<string> GetElementPrimaryPropertyNames(string schema, string elementName)
        {
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSI = DataSource.CreateQuery<SqlServerIndexesTableElement>();
            var qcSK = DataSource.CreateQuery<SqlServerIndexKeysTableElement>();
            var qcSC = DataSource.CreateQuery<SqlServerColumnsTableElement>();

            var qc1 = from sch in qcSS
                      join obj in qcSO on sch.SchemaID equals obj.SchemaID
                      where sch.Name == schema && obj.Name == elementName && (obj.Type == "U" || obj.Type == "V")
                      select obj.ObjectID;

            var objectID = qc1.FirstOrDefault();

            var qc2 = from obj in qcSO
                      join index in qcSI on obj.Name equals index.Name
                      join key in qcSK on index.IndexID equals key.IndexID
                      where obj.Type == "PK" && obj.ParentObjectID == objectID && key.ObjectID == objectID
                      select new
                      {
                          key.ColumnID
                      };

            var qc3 = from key in qc2
                      join col in qcSC on key.ColumnID equals col.ColumnID
                      where col.ObjectID == objectID
                      select col.Name;

            return qc3.ToList();
        }

        public override List<DataColumnAttribute> GetElementProperties(string schema, string tableName)
        {
            if (!AnyElement(schema, tableName))
                throw new ElementNotFoundException(schema, tableName);

            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSC = DataSource.CreateQuery<SqlServerColumnsTableElement>();
            var qcST = DataSource.CreateQuery<SqlServerTypesTableElement>();
            var qcSE = DataSource.CreateQuery<SqlServerExtendsTableElement>();

            var qcCol = from sch in qcSS
                        join obj in qcSO on sch.SchemaID equals obj.SchemaID
                        join col in qcSC on obj.ObjectID equals col.ObjectID
                        join type in qcST on col.TypeID equals type.TypeID
                        where sch.Name == schema && obj.Name == tableName && (obj.Type == "U" || obj.Type == "V") && type.Name != "sysname"
                        select new
                        {
                            col.ColumnID,
                            col.Name,
                            col.Auto,
                            col.Nullable,
                            col.Precision,
                            col.Scale,
                            col.Size,
                            Type = type.Name
                        };

            var qcExt = from sch in qcSS
                        join obj in qcSO on sch.SchemaID equals obj.SchemaID
                        join ext in qcSE on obj.ObjectID equals ext.ObjectID
                        where sch.Name == schema && obj.Name == tableName &&
                             (obj.Type == "U" || obj.Type == "V") &&
                             ext.Name == stringExtendAliasName
                        select new
                        {
                            ext.ColumnID,
                            AliasName = ext.Value,
                        };

            var qc = from col in qcCol
                     from ext in qcExt
                     where col.ColumnID == ext.ColumnID
                     select new
                     {
                         col.Name,
                         col.Auto,
                         col.Nullable,
                         col.Precision,
                         col.Scale,
                         col.Size,
                         col.Type,
                         ext.AliasName,
                     };

            var cols = qc.ToList();

            var factory = DataSource.DataSource.CreateScriptFactory();
            var attrs = new List<DataColumnAttribute>();
            var keys = GetElementPrimaryPropertyNames(schema, tableName);

            foreach (var col in cols)
                attrs.Add(new DataColumnAttribute(
                    col.Name, factory.GetDataType(col.Type),
                    col.Type == "image" ? -1 : col.Size,
                    col.Precision, col.Scale,
                    keys.Contains(col.Name),
                    col.Nullable, col.Auto, true)
                    {
                        AliasName = col.AliasName,
                        MemberName = col.Name
                    });

            return attrs;
        }

        public override List<DataTableAttribute> GetElements()
        {
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSE = DataSource.CreateQuery<SqlServerExtendsTableElement>();

            var qcTab = from sch in qcSS
                        join obj in qcSO on sch.SchemaID equals obj.SchemaID
                        where (obj.Type == "U")
                        select new
                        {
                            obj.ObjectID,
                            Schema = sch.Name,
                            TableName = obj.Name,
                        };

            var qcExt = from sch in qcSS
                        join obj in qcSO on sch.SchemaID equals obj.SchemaID
                        join ext in qcSE on obj.ObjectID equals ext.ObjectID
                        where obj.Type == "U" &&
                              ext.ColumnID == 0 &&
                              ext.Name == stringExtendAliasName
                        select new
                        {
                            ext.ObjectID,
                            AliasName = ext.Value,
                        };

            var qc = from tab in qcTab
                     from ext in qcExt
                     where tab.ObjectID == ext.ObjectID
                     select new
                     {
                         tab.Schema,
                         tab.TableName,
                         ext.AliasName,
                     };

            var tabs = qc.ToList();

            var attrs = new List<DataTableAttribute>();
            foreach (var tab in tabs)
                attrs.Add(new DataTableAttribute(tab.Schema, tab.TableName) { AliasName = tab.AliasName });

            return attrs;
        }

        public override List<ElementRelationship> GetElementRelationshipsDirection()
        {
            var qcSO = DataSource.CreateQuery<SqlServerObjectsTableElement>();
            var qcSS = DataSource.CreateQuery<SqlServerSchemasTableElement>();
            var qcSC = DataSource.CreateQuery<SqlServerColumnsTableElement>();
            var qcFC = DataSource.CreateQuery<SqlServerForeignKeyColumnsTableElement>();

            var qcPO = from fc in qcFC
                       join so in qcSO on fc.PrimaryObjectID equals so.ObjectID
                       join ss in qcSS on so.SchemaID equals ss.SchemaID
                       where fc.ForeignKeyObjectID != null
                       select new
                       {
                           Key = fc.ForeignKeyObjectID,
                           PrimarySchema = ss.Name,
                           PrimaryElementName = so.Name,
                       };

            var qcPC = from fc in qcFC
                       join sc in qcSC on fc.PrimaryObjectID equals sc.ObjectID
                       where fc.PrimaryColumnID == sc.ColumnID
                       select new
                       {
                           Key = fc.ForeignKeyObjectID,
                           PrimaryColumnName = sc.Name,
                       };

            var qcFRO = from fc in qcFC
                        join so in qcSO on fc.ForeignObjectID equals so.ObjectID
                        join ss in qcSS on so.SchemaID equals ss.SchemaID
                        where fc.ForeignKeyObjectID != null
                        select new
                        {
                            Key = fc.ForeignKeyObjectID,
                            ForeignSchema = ss.Name,
                            ForeignElementName = so.Name,
                        };

            var qcFRC = from fc in qcFC
                        join sc in qcSC on fc.ForeignObjectID equals sc.ObjectID
                        where fc.ForeignColumnID == sc.ColumnID
                        select new
                        {
                            Key = fc.ForeignKeyObjectID,
                            ForeignColumnName = sc.Name,
                        };

            var qc = from po in qcPO
                     join pc in qcPC on po.Key equals pc.Key
                     join fro in qcFRO on po.Key equals fro.Key
                     join frc in qcFRC on po.Key equals frc.Key
                     where po.Key != null
                     select new ElementRelationship
                     {
                         PrimarySchema = po.PrimarySchema,
                         PrimaryElementName = po.PrimaryElementName,
                         PrimaryColumnName = pc.PrimaryColumnName,
                         ForeignSchema = fro.ForeignSchema,
                         ForeignElementName = fro.ForeignElementName,
                         ForeignColumnName = frc.ForeignColumnName,
                     };

            return qc.ToList();
        }

        #endregion

        #region Methods - Private

        #endregion

        #endregion
    }
    [DataTable(TableName = "objects", Schema = "sys")]
    public class SqlServerObjectsTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "object_id")]
        public int ObjectID { get; set; }
        [DataColumn(ColumnName = "parent_object_id")]
        public int ParentObjectID { get; set; }
        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "type")]
        public string Type { get; set; }
        [DataColumn(ColumnName = "schema_id")]
        public int SchemaID { get; set; }

        #endregion
    }
    [DataTable(TableName = "schemas", Schema = "sys")]
    public class SqlServerSchemasTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "schema_id")]
        public int SchemaID { get; set; }

        #endregion
    }
    [DataTable(TableName = "columns", Schema = "sys")]
    public class SqlServerColumnsTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "column_id")]
        public int ColumnID { get; set; }
        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "object_id")]
        public int ObjectID { get; set; }
        [DataColumn(ColumnName = "user_type_id")]
        public int TypeID { get; set; }
        [DataColumn(ColumnName = "max_length")]
        public int Size { get; set; }
        [DataColumn(ColumnName = "precision")]
        public int Precision { get; set; }
        [DataColumn(ColumnName = "scale")]
        public int Scale { get; set; }
        [DataColumn(ColumnName = "is_nullable")]
        public bool Nullable { get; set; }
        [DataColumn(ColumnName = "is_identity")]
        public bool Auto { get; set; }

        #endregion
    }
    [DataTable(TableName = "sysindexes", Schema = "sys")]
    public class SqlServerIndexesTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "indid")]
        public int IndexID { get; set; }
        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }

        #endregion
    }
    [DataTable(TableName = "sysindexkeys", Schema = "sys")]
    public class SqlServerIndexKeysTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "indid")]
        public int IndexID { get; set; }
        [DataColumn(ColumnName = "id")]
        public int ObjectID { get; set; }
        [DataColumn(ColumnName = "colid")]
        public int ColumnID { get; set; }

        #endregion
    }
    public class ElementNotFoundException : Exception
    {
        #region Ctor

        static ElementNotFoundException()
        {
            //LanguageAttribute.AddLanguage(Properties.Resources.langChs_Exception);
        }

        public ElementNotFoundException(string schema, string element)
            : this(schema, element, null)
        {
        }

        public ElementNotFoundException(string schema, string element, Exception innerException)
            : base(schema + " " + element, innerException)
        {
        }

        #endregion
    }
    [DataTable(TableName = "systypes", Schema = "sys")]
    public class SqlServerTypesTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "xusertype")]
        public int TypeID { get; set; }

        #endregion
    }
    [DataTable(TableName = "extended_properties", Schema = "sys")]
    public class SqlServerExtendsTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "name")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "value")]
        public string Value { get; set; }
        [DataColumn(ColumnName = "major_id")]
        public int ObjectID { get; set; }
        [DataColumn(ColumnName = "minor_id")]
        public int ColumnID { get; set; }

        #endregion
    }
    [DataTable(TableName = "foreign_key_columns", Schema = "sys")]
    public class SqlServerForeignKeyColumnsTableElement : CDObject
    {
        #region Properties

        [DataColumn(ColumnName = "constraint_object_id")]
        public int ForeignKeyObjectID { get; set; }

        [DataColumn(ColumnName = "referenced_object_id")]
        public int PrimaryObjectID { get; set; }
        [DataColumn(ColumnName = "referenced_column_id")]
        public int PrimaryColumnID { get; set; }

        [DataColumn(ColumnName = "parent_object_id")]
        public int ForeignObjectID { get; set; }
        [DataColumn(ColumnName = "parent_column_id")]
        public int ForeignColumnID { get; set; }

        #endregion
    }
}
